#!/usr/bin/perl

#usage: perl <path>/obskml_to_xenia_sqlite.pl <input zipped kml files> <new_platform_handle concatened by .> <optional: debug output sql directory> <optional: output sql filename prefix> >> <log file>
#note if using debug output sql directory to create a 'archive_in' subdirectory there also which is written to in the script below

#usage: perl /var/www/cgi-bin/microwfs/obskml_to_xenia_sqlite.pl http://carocoops.org/obskml/feeds/seacoos_all_latest.zip >>/tmp/microwfs_debug.log 2>/dev/null

#usage debug: perl /var/www/cgi-bin/microwfs/obskml_to_xenia_sqlite.pl http://carocoops.org/obskml/feeds/seacoos_all_latest.zip /mydir >>/tmp/microwfs_debug.log 2>/dev/null

#note the user process under which this runs needs to have permissions to write to the realted path

use DBI;
use XML::LibXML;
use strict;
use Config::IniFiles;

#log run out
my $date_now = `date +%Y-%m-%dT%H:%M:%S`;
chomp($date_now);
print "obskml_to_xenia_sqlite.pl run start $date_now\n";

#a temporary directory for decompressing, processing files
#my $temp_dir = '/tmp/ms_tmp';

#originally resolving seconds for dropping duplicates, but dropping time seconds resolution due to duplicates(:00, :40 secs) which are probably netcdf truncating related
#see usage in code below

#open (SQL_OUT, ">$path_sqlfile");
#open (SQL_OUT_ARCHIVE, ">$path_sqlfile_archive");
#print SQL_OUT "BEGIN IMMEDIATE TRANSACTION;\n";


##################
#read input files 
##################
my $kml_zip_feed=$ARGV[1];
my $orga=$ARGV[0];
my $temp="/tmp/$orga";
system("rm /tmp/$orga/*.kml");
system("unzip $kml_zip_feed -d $temp");
my $filelist = `ls $temp/*.kml`;

my @files = split(/\n/,$filelist);

#Database connection
my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $tname=$cfg->val('rcoos','tname');
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');
my $dbh = DBI->connect ("dbi:Pg:dbname=$db_name","$db_user","$db_passwd");
if(!defined $dbh) {die "Cannot connect to database!\n";}
my ($sql,$sth);

####initialize lookup hash for sub get_m_type_id
my $undefined_platform_type_id=6;
my %m_type_lookup = ();

   $sql = qq{ select t0.row_id,t2.standard_name,t3.standard_name from m_type t0,
              m_scalar_type t1,obs_type t2,uom_type t3 where t0.m_scalar_type_id = t1.row_id 
              and t0.num_types = 1 and t1.obs_type_id = t2.row_id and t1.uom_type_id = t3.row_id };
   $sth = $dbh->prepare( $sql );
   $sth->execute();

   while (my ($row_id,$obs_type,$uom) = $sth->fetchrow_array) {
        $m_type_lookup{$obs_type.".".$uom} = $row_id;
   };

   my @valid_platform;

   $sql = qq{ select platform_handle from platform where active=true };
   $sth = $dbh->prepare( $sql );
   $sth->execute();

   while (my ($plat) = $sth->fetchrow_array) {
        push(@valid_platform,$plat);
   }
   my @new_plat;
   if($ARGV[1]){ 
      @new_plat= split(/./,$ARGV[1]);
      foreach my $p (@new_plat){
         if(grep $_ eq $p,@valid_platform){
            ;
         }
         else {
             push(@valid_platform,$p);
         }
      }
   }
   
  my $measurement_count=0;

   foreach my $file (@files) {
       my $xp = XML::LibXML->new->parse_file("$file");
       print "file:$xp\n";

       foreach my $platform ($xp->findnodes('//Placemark')) {

           #get org/platform related info - only processing obskml with Placemark id attribute utilized (like 'Placemark id=carocoops.CAP1.wls')
           my $platform_id = sprintf("%s",$platform->getAttribute('id'));
           #if platform_id not in id attribute, look in name field
           if (!($platform_id)) { $platform_id = sprintf("%s",$platform->find('name')); }

           my ($organization_name,$platform_name,$platform_type) = split(/[\._]/,$platform_id);
           $platform_id = "$organization_name\.$platform_name\.$platform_type";

           print "$platform_id\n";

           #could develop a generic 'none.none.none' placeholder, but problems with maintenance and clean-up with temporary platform,sensor ids
           if (!($platform_id)) { print "no platform found for $file"; next; }
           
           #skip platform_id in platform_ignore list      
           if (grep $_ eq $platform_id,@valid_platform) { ; }
           else { next;}

           my $operator_url = sprintf("%s",$platform->find('Metadata/obsList/operatorURL'));
           $operator_url = escape_literals($operator_url);
           my $platform_url = sprintf("%s",$platform->find('Metadata/obsList/platformURL'));
           $platform_url = escape_literals($platform_url);
           my $platform_desc = sprintf("%s",$platform->find('Metadata/obsList/platformDescription'));
           $platform_desc = escape_literals($platform_desc);

           my $lon_lat_string = sprintf("%s",$platform->find('Point/coordinates'));
           my ($longitude,$latitude) = split(/,/,$lon_lat_string);
           #only carry lon/lat precision to six decimal places
           $longitude = sprintf("%.6f", $longitude);
           $latitude = sprintf("%.6f", $latitude);
           print "$longitude $latitude \n";
       
           my $m_date = sprintf("%s",$platform->find('TimeStamp/when'));
           #print "$m_date :when \n";

           #calculate seconds to add to base time to get zulu time
           #note the below conversion could also be done using sqlite database command like
           #select datetime('2008-01-01T12:00:00','+$timezone hours')

            my ($timezone) = substr($m_date,19,3);
            $m_date = substr($m_date,0,19); #drop timezone for 'date' command below
            $m_date =~ s/T/ /; #swap space for 'T' for 'date' command below
            $m_date="'$m_date'";
            if($timezone){   
               $timezone = -1 * $timezone;
               $m_date=$m_date."+interval '$timezone hour'";
            }  

            ##organization####################
            $sql = qq{ SELECT row_id,short_name from organization where short_name like '$organization_name' };
            #print $sql."\n";
            $sth = $dbh->prepare( $sql );
            $sth->execute();
            my ($organization_row_id,$organization_name_lkp) = $sth->fetchrow_array;

            if ($organization_name_lkp) {} #print "$organization_name_lkp\n";
            else {
                print "organization $organization_name not found - inserting\n";
       
                $sql = qq{ INSERT into organization(row_entry_date,row_update_date,active,short_name,url) values (current_timestamp,current_timestamp,true,'$organization_name','$operator_url'); };
                #print $sql."\n";
                $sth = $dbh->prepare( $sql );
                $sth->execute();

                #requery for new row_id
                $sql = qq{ SELECT row_id from organization where short_name like '$organization_name' };
                #print $sql."\n";
                $sth = $dbh->prepare( $sql );
                $sth->execute();
                ($organization_row_id) = $sth->fetchrow_array;
             }

             ##platform####################
             $sql = qq{ SELECT row_id,short_name from platform where organization_id = $organization_row_id and platform_handle like '$platform_id' };
             #print $sql."\n";
             $sth = $dbh->prepare( $sql );
             $sth->execute();
             my ($platform_row_id,$platform_name_lkp) = $sth->fetchrow_array;

             if ($platform_name_lkp) {} #print "$platform_name_lkp\n"; }
             else {
                print "platform $platform_name not found - inserting\n";

                $sql = qq{ INSERT into platform(row_entry_date,row_update_date,organization_id,type_id,short_name,platform_handle,fixed_longitude,fixed_latitude,long_name,description,url) values (current_timestamp,current_timestamp,$organization_row_id,$undefined_platform_type_id,'$platform_name','$platform_id',$longitude,$latitude,'$platform_desc','$platform_desc','$platform_url'); };
                #print $sql."\n";
                $sth = $dbh->prepare( $sql );
                $sth->execute();

                #requery for new row_id
                $sql = qq{ SELECT row_id from platform where organization_id = $organization_row_id and platform_handle like '$platform_id' };
                #print $sql."\n";
                $sth = $dbh->prepare( $sql );
                $sth->execute();
                ($platform_row_id) = $sth->fetchrow_array;
             }

             foreach my $observation ($platform->findnodes('Metadata/obsList/obs')) {
  
                my $obs_type = sprintf("%s",$observation->find('obsType'));
                my $uom_type = sprintf("%s",$observation->find('uomType'));
                my $measurement = sprintf("%s",$observation->find('value'));
                if(($obs_type eq "current_speed")&&($uom_type eq "m_s-1")){
                     $uom_type="cm_s-1";
                     $measurement=100*$measurement;
                }
                my $elev = sprintf("%s",$observation->find('elev'));
                my $sorder = sprintf("%s",$observation->find('sorder'));
                my $qclevel = sprintf("%s",$observation->find('QCLevel'));

                if(($obs_type eq "northward_current")||($obs_type eq "eastward_current")){
                     next;    
                }

                #with latest version of database that does not include NOT NULL constraints for sensor.type_id and sensor.short_name could shorten below statements to just utilize m_type_id

                my $m_type_id = get_m_type_id($obs_type,$uom_type);
                if ($m_type_id eq '') { print "$obs_type.$uom_type undefined \n"; next; }

                ##sensor####################

                if ($sorder eq '') { $sorder = 1; }

                $sql = qq{ SELECT row_id from sensor where m_type_id = $m_type_id and platform_id = $platform_row_id and s_order = $sorder };
                #print $sql."\n";
                $sth = $dbh->prepare( $sql );
                $sth->execute();
                my ($sensor_row_id) = $sth->fetchrow_array;

                if ($sensor_row_id) {} #print "$sensor_row_id\n"; }
                else {
                     print "sensor $obs_type.$uom_type $platform_name s_order:$sorder m_type_is: $m_type_id not found - inserting\n";

                     $sql = qq{ SELECT row_id from sensor_type where type_name='$obs_type' };
                     $sth = $dbh->prepare( $sql );
                     $sth->execute();
                     my ($sensor_type_id) = $sth->fetchrow_array;

                     $sql = qq{ INSERT into sensor(row_entry_date,row_update_date,platform_id,type_id,short_name,m_type_id,s_order,display) values (current_timestamp,current_timestamp,$platform_row_id,$sensor_type_id,'$obs_type',$m_type_id,$sorder,3); };
                     #print $sql."\n";
                     $sth = $dbh->prepare( $sql );
                     $sth->execute();

                     #requery for new row_id
                     $sql = qq{ SELECT row_id from sensor where m_type_id = $m_type_id and platform_id = $platform_row_id };
                    #print $sql."\n";
                     $sth = $dbh->prepare( $sql );
                     $sth->execute();
                     ($sensor_row_id) = $sth->fetchrow_array;

                 }

                 ##observation####################

                 if ($elev eq '') { $elev = -99999; }

                 if($measurement){
                    my $f=0;
                    #ignore the wave data from CORMP ILM2 since we are getting the same from cdip website 
                    if(($sensor_row_id==5436)||($sensor_row_id==5435)){
                       $f=1;
                    }
                    #ignore OCP1 extra wave data
                    if(($sensor_row_id==4845)||($sensor_row_id==4846)||($sensor_row_id==5105)||($sensor_row_id==5104)){
                       #  if(substr($m_date,15,2)!='00'){ $f=1;}
                        $f=1;
                    }

                     if($f==0){
                         
                          $sql = qq{ INSERT into $tname (row_entry_date,row_update_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value) values (current_timestamp,current_timestamp,'$platform_id',$sensor_row_id,$m_type_id,$m_date,$longitude,$latitude,$elev,$measurement); };
                          $sth=$dbh->prepare($sql);
                          #print substr($m_date,14,2)."--$sensor_row_id\n";
                          $sth->execute();
                      }
                   
                }
        } #foreach $observation


        $sth->finish();
        
    } #foreach $platform

} #foreach $file

$dbh->disconnect();

exit 0;

####################################################

sub get_m_type_id {
  my ($obs_type,$uom) = @_;

  ##CONFIG_START
  #conversions for the following which were labeled wrong on the way in to db
  if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
  if ($obs_type eq "water_depth"){$obs_type="water_level";}
  if ($uom eq 'millibar') { $uom = 'mb'; }
  if ($uom eq 'percent_saturation') { $uom = 'percent'; }
  if ($uom eq 'm(MLLW)') { $uom = 'm'; }
  #JTC 2009-09-25
  if ($obs_type eq 'oxygen_concentration' && $uom eq 'percent') { $obs_type = 'oxygen_saturation'; }
##CONFIG_END

  my $ret_val = $m_type_lookup{$obs_type.".".$uom};
  return $ret_val;
}

#--------------------------------------------------------------------
#                   escape_literals
#--------------------------------------------------------------------
# Must make sure values don't contain XML reserved chars
sub escape_literals {
 my $str = shift;
 $str =~ s/</&lt;/gs;
 $str =~ s/>/&gt;/gs;
 $str =~ s/&/&amp;/gs;
 $str =~ s/"/&quot;/gs;
 $str =~ s/'/&#39;/gs;
 return ($str);
}
